<?php
/**
 * Database Cleanup and Standardization Script
 * Rename all tables to use gf_ prefix and remove invalid ones
 * 
 * Usage: php cleanup_and_standardize_tables.php
 */

// Load environment variables from .env file
$envFile = __DIR__ . '/../../.env';
if (file_exists($envFile)) {
    $lines = file($envFile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
    foreach ($lines as $line) {
        if (strpos($line, '=') !== false && strpos($line, '#') !== 0) {
            list($key, $value) = explode('=', $line, 2);
            $_ENV[trim($key)] = trim($value);
        }
    }
}

// Database configuration
try {
    $host = $_ENV['DB_HOST'] ?? '127.0.0.1';
    $port = $_ENV['DB_PORT'] ?? 3306;
    $dbname = $_ENV['DB_DATABASE'] ?? 'XappTool';
    $username = $_ENV['DB_USERNAME'] ?? 'root';
    $password = $_ENV['DB_PASSWORD'] ?? '';
    
    echo "🧹 Cleaning up and standardizing database: $host:$port/$dbname\n";
    echo "Using username: $username\n\n";
    
    $pdo = new PDO("mysql:host=$host;port=$port;dbname=$dbname;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "✅ Database connection established successfully.\n\n";
    
    // Tables to rename (non-gf_ prefix tables)
    $tablesToRename = [
        'anti_fraud' => 'gf_anti_fraud',
        'api_keys' => 'gf_api_keys',
        'apps' => 'gf_apps',
        'attribution' => 'gf_attribution',
        'et_apps' => 'gf_et_apps',
        'et_apps_apps' => 'gf_et_apps_apps',
        'et_apps_event' => 'gf_et_apps_event',
        'et_apps_web' => 'gf_et_apps_web',
        'et_system_email' => 'gf_et_system_email',
        'et_system_email_config' => 'gf_et_system_email_config',
        'et_users' => 'gf_et_users',
        'fingerprints' => 'gf_fingerprints',
        'merchants' => 'gf_merchants',
        'stats_daily' => 'gf_stats_daily'
    ];
    
    // Tables to potentially drop (duplicates, test tables, etc.)
    // These need manual review - we'll list them but not auto-delete
    $tablesToReview = [
        'et_apps_apps' => 'Possible duplicate of et_apps',
        'et_system_email_config' => 'Possible duplicate of et_system_email',
        'stats_daily' => 'Check if this is still needed'
    ];
    
    echo "📋 ANALYSIS RESULTS:\n";
    echo "==================================================\n\n";
    
    // Get all current tables
    $stmt = $pdo->query("SHOW TABLES");
    $allTables = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    echo "Total tables found: " . count($allTables) . "\n";
    echo "Tables with gf_ prefix: " . count(array_filter($allTables, function($t) { return strpos($t, 'gf_') === 0; })) . "\n";
    echo "Tables without gf_ prefix: " . count(array_filter($allTables, function($t) { return strpos($t, 'gf_') !== 0; })) . "\n\n";
    
    // Show current table structure
    echo "Current tables:\n";
    foreach ($allTables as $table) {
        $prefix = strpos($table, 'gf_') === 0 ? "✅" : "❌";
        echo "  $prefix $table\n";
    }
    echo "\n";
    
    // Ask for confirmation before proceeding
    echo "🚨 IMPORTANT: This script will:\n";
    echo "1. Rename all non-gf_ tables to use gf_ prefix\n";
    echo "2. List potentially redundant tables for manual review\n";
    echo "3. Generate migration scripts\n";
    echo "4. Export final SQL structure\n\n";
    
    echo "Tables to be renamed:\n";
    foreach ($tablesToRename as $old => $new) {
        echo "  - $old → $new\n";
    }
    echo "\n";
    
    // For safety, we'll proceed with renaming but not dropping
    echo "🔄 Starting table renaming process...\n\n";
    
    $successCount = 0;
    $errorCount = 0;
    $skippedCount = 0;
    
    foreach ($tablesToRename as $oldName => $newName) {
        echo "Processing: $oldName → $newName\n";
        
        try {
            // Check if old table exists
            $checkStmt = $pdo->prepare("SHOW TABLES LIKE ?");
            $checkStmt->execute([$oldName]);
            $oldTableExists = $checkStmt->fetch();
            
            if (!$oldTableExists) {
                echo "  ⚠️  Table '$oldName' does not exist, skipping...\n";
                $skippedCount++;
                continue;
            }
            
            // Check if new table already exists
            $checkStmt = $pdo->prepare("SHOW TABLES LIKE ?");
            $checkStmt->execute([$newName]);
            $newTableExists = $checkStmt->fetch();
            
            if ($newTableExists) {
                echo "  ⚠️  Table '$newName' already exists, skipping rename...\n";
                $skippedCount++;
                continue;
            }
            
            // Rename table
            $renameSql = "RENAME TABLE `$oldName` TO `$newName`";
            $pdo->exec($renameSql);
            
            echo "  ✅ Successfully renamed table '$oldName' to '$newName'\n";
            $successCount++;
            
        } catch (PDOException $e) {
            echo "  ❌ Error renaming table '$oldName' to '$newName': " . $e->getMessage() . "\n";
            $errorCount++;
        }
        
        echo "\n";
    }
    
    echo "================ RENAMING SUMMARY ================\n";
    echo "✅ Successfully renamed: $successCount tables\n";
    echo "⚠️  Skipped: $skippedCount tables\n";
    echo "❌ Errors encountered: $errorCount tables\n";
    echo "==================================================\n\n";
    
    // Get final table list
    echo "📋 FINAL TABLE STATUS:\n";
    echo "==================================================\n";
    $stmt = $pdo->query("SHOW TABLES");
    $finalTables = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    $gfFinalTables = array_filter($finalTables, function($t) { return strpos($t, 'gf_') === 0; });
    $nonGfFinalTables = array_filter($finalTables, function($t) { return strpos($t, 'gf_') !== 0; });
    
    echo "Total tables: " . count($finalTables) . "\n";
    echo "Tables with gf_ prefix: " . count($gfFinalTables) . " ✅\n";
    echo "Tables without gf_ prefix: " . count($nonGfFinalTables) . " ❌\n\n";
    
    echo "All tables:\n";
    foreach ($finalTables as $table) {
        $status = strpos($table, 'gf_') === 0 ? "✅" : "❌";
        echo "  $status $table\n";
    }
    echo "\n";
    
    // Generate migration script
    echo "📝 Generating migration script...\n";
    generateMigrationScript($finalTables);
    
    // Export SQL structure
    echo "💾 Exporting SQL structure...\n";
    exportSQLStructure($pdo, $finalTables);
    
    echo "\n🎉 Database cleanup and standardization completed!\n";
    
} catch (PDOException $e) {
    echo "❌ Database connection failed: " . $e->getMessage() . "\n";
    exit(1);
}

/**
 * Generate migration script
 */
function generateMigrationScript($tables) {
    $migrationContent = "<?php\n";
    $migrationContent .= "/**\n";
    $migrationContent .= " * Database Migration Script\n";
    $migrationContent .= " * Auto-generated table structure for gf_ prefixed tables\n";
    $migrationContent .= " * Generated at: " . date('Y-m-d H:i:s') . "\n";
    $migrationContent .= " */\n\n";
    
    $migrationContent .= "return [\n";
    
    foreach ($tables as $table) {
        if (strpos($table, 'gf_') === 0) {
            $migrationContent .= "    // Table: $table\n";
            $migrationContent .= "    '$table' => [\n";
            $migrationContent .= "        'name' => '$table',\n";
            $migrationContent .= "        'prefix' => 'gf_',\n";
            $migrationContent .= "        'status' => 'active'\n";
            $migrationContent .= "    ],\n\n";
        }
    }
    
    $migrationContent .= "];\n";
    
    file_put_contents('gf_tables_migration.php', $migrationContent);
    echo "  ✅ Migration script saved to: gf_tables_migration.php\n";
}

/**
 * Export SQL structure
 */
function exportSQLStructure($pdo, $tables) {
    $sqlContent = "-- Database Structure Export\n";
    $sqlContent .= "-- Generated at: " . date('Y-m-d H:i:s') . "\n";
    $sqlContent .= "-- Database: Standardized with gf_ prefix\n\n";
    
    $sqlContent .= "SET FOREIGN_KEY_CHECKS = 0;\n\n";
    
    foreach ($tables as $table) {
        if (strpos($table, 'gf_') === 0) {
            try {
                // Get CREATE TABLE statement
                $stmt = $pdo->query("SHOW CREATE TABLE `$table`");
                $result = $stmt->fetch(PDO::FETCH_ASSOC);
                
                $sqlContent .= "-- Table structure for table `$table`\n";
                $sqlContent .= "DROP TABLE IF EXISTS `$table`;\n";
                $sqlContent .= $result['Create Table'] . ";\n\n";
                
                echo "  ✅ Exported table: $table\n";
            } catch (PDOException $e) {
                echo "  ❌ Error exporting table $table: " . $e->getMessage() . "\n";
            }
        }
    }
    
    $sqlContent .= "SET FOREIGN_KEY_CHECKS = 1;\n";
    
    file_put_contents('gf_database_structure.sql', $sqlContent);
    echo "  ✅ SQL structure saved to: gf_database_structure.sql\n";
}

?>